/* $RCSFile: install.sql $
 Apache license v2 2011 by Christian Surieux
 Removing this comment is forbiddden
 $Source: ?/AddonNice.ImageManager/PageModules/CRMImageBook/install.sql $
 $log$
 Revision 38 2010/08/09 12:28:18  christian.surieux
   Adding Log comment to sql files
*/
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[adn_UpdateCRMImageBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_UpdateCRMImageBook]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_AddCRMImageBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_AddCRMImageBook]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_DeleteAllCRMImageBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_DeleteAllCRMImageBook]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_DeleteCRMImageBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_DeleteCRMImageBook]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_GetCRMImageBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImageBook]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_GetCRMImageBookFull]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImageBookFull]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_GetCRMImageBookFullPaged]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImageBookFullPaged]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_GetSingleCRMImageBook]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetSingleCRMImageBook]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_GetCRMImageBookNb]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMImageBookNb]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_GetCRMAllImageBookPaged]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetCRMAllImageBookPaged]
GO

IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_GetAllCRMImagesEx]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[adn_GetAllCRMImagesEx]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[dbo].[adn_CRMImageBook]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[adn_CRMImageBook]
(
	[EntityName] [nchar](50) NOT NULL,
	[CRMId] [uniqueidentifier] NOT NULL,
	[ImageId] [int] NOT NULL,
	[DisplayOrder] [int] NOT NULL,
	[ShortDescription] [nvarchar] (512) NULL ,
	[MetadataXml] [xml] NULL ,
 CONSTRAINT [PK_adn_CRMImageBook] PRIMARY KEY NONCLUSTERED 
(
	[EntityName] ASC,
	[CRMId] ASC,
	[ImageId] ASC
) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[adn_CRMImageBook]  WITH CHECK ADD  CONSTRAINT [FK_adn_CRMImageBook_adn_CRMImage2] FOREIGN KEY([ImageId])
REFERENCES [dbo].[adn_CRMImage2] ([ImageId])
END
GO


CREATE PROCEDURE [dbo].[adn_UpdateCRMImageBook]
(
		@EntityName 	nchar (50) ,
		@CRMId 	[uniqueidentifier ],
		@DisplayOrder [int],
		@ImageId 	[int ],
		@OldImageId 	[int ],
		@ShortDescription [nvarchar] (512),
		@MetadataXml [xml] 
)
AS 
UPDATE [adn_CRMImageBook]
SET  
	[ImageId]		=	@ImageId,
	[DisplayOrder]	=	@DisplayOrder,
	ShortDescription=	@ShortDescription,
	MetadataXml		=	@MetadataXml
WHERE [EntityName]	= @EntityName AND [CRMId] =	@CRMId AND [ImageId] =	@OldImageId
GO

CREATE PROCEDURE [dbo].[adn_AddCRMImageBook]
(	
	@EntityName 	nchar (50) ,
	@CRMId 			[uniqueidentifier ],
	@DisplayOrder [int],
	@ImageId 		[int ] ,
	@ShortDescription [nvarchar] (512),
	@MetadataXml [xml] 
)
AS 
INSERT INTO [adn_CRMImageBook]
(
	[EntityName],
	[CRMId],
	[DisplayOrder],
	[ImageId],
	[ShortDescription],
	[MetadataXml]
) 
VALUES 
	(
	 @EntityName,
	 @CRMId,
	 @DisplayOrder,
	 @ImageId,
	 @ShortDescription,
	 @MetadataXml
)
GO

CREATE PROCEDURE [dbo].[adn_DeleteAllCRMImageBook]
(
	@EntityName 	nchar (50) ,
	@CRMId 			[uniqueidentifier ]
)
AS DELETE FROM [adn_CRMImageBook]
WHERE ( EntityName = @EntityName AND CRMId = @CRMId )
GO

CREATE PROCEDURE [dbo].[adn_DeleteCRMImageBook]
(
	@EntityName 	nchar (50) ,
	@CRMId 			[uniqueidentifier ],
	@ImageId 		[int ] 
)
AS DELETE FROM [adn_CRMImageBook]
WHERE ( [ImageId] = @ImageId AND EntityName = @EntityName AND CRMId = @CRMId )
GO

CREATE PROCEDURE [dbo].[adn_GetCRMImageBook]
(
	@EntityName 	nchar (50) ,
	@CRMId 		[uniqueidentifier] 
	)
AS
	SELECT 
		CRMId,
		ImageId,
		DisplayOrder,
		ShortDescription,
		MetadataXml
	FROM adn_CRMImageBook WHERE EntityName = @EntityName AND CRMId = @CRMId ORDER BY DisplayOrder
GO


CREATE PROCEDURE [dbo].[adn_GetCRMImageBookFull]
(
	@EntityName 	nchar (50) ,
	@CRMId 		[uniqueidentifier] 
	)
AS
	SELECT 
		ib.CRMId,
		ib.DisplayOrder,
		ib.ImageId,
		im.[CrmImageId],
		im.[Caption],
		im.[Category],
		im.[Keywords],
		im.[CreatedByUser],
		im.[CreatedDate],
		im.[UpdatedByUser],
		im.[UpdateDate],
		ib.[ShortDescription],
		ib.[MetadataXml]
	FROM adn_CRMImageBook AS ib	LEFT OUTER JOIN adn_CRMImage2 AS im ON ib.ImageId = im.imageId 
		WHERE ib.EntityName = @EntityName AND ib.CRMId = @CRMId ORDER BY DisplayOrder
GO


CREATE PROCEDURE [dbo].[adn_GetCRMImageBookFullPaged]
(
	@Page int = 1,
	@RecordsPerPage int = 10,
	@EntityName 	nchar (50) ,
	@CRMId 		[uniqueidentifier] 
)
AS
BEGIN
-- Find out the first AND last record we want
DECLARE @FirstRec int, @LastRec int

--Create a temporary table
CREATE TABLE #TempItems
(
	ID		int IDENTITY,
	EntityName 	nchar (50) ,
	CRMId 		[uniqueidentifier],
	[ImageId] [int] NOT NULL,
	[DisplayOrder] [int] NOT NULL,
	[CrmImageId] [uniqueidentifier] NULL ,
	[Caption] [nvarchar] (256) NULL ,
	[Category] [int] not NULL ,
	[Keywords] [nvarchar] (256) NULL ,
	[CreatedByUser] [nvarchar] (100) NULL ,
	[CreatedDate] [datetime] not NULL ,
	[UpdatedByUser] [nvarchar] (100) NULL ,
	[UpdateDate] [datetime] not NULL ,
	[ShortDescription] [nvarchar] (512) NULL ,
	[MetadataXml] [xml] NULL 
)

	-- We don't want to RETURN the # of rows inserted
	-- into our temporary table, so turn NOCOUNT ON
	SET NOCOUNT ON

	-- INSERT the rows FROM tblItems into the temp. table
	INSERT INTO	#TempItems
	(
		EntityName, 
		CRMId,
		[ImageId],
		[DisplayOrder],
		[CrmImageId],
		[Caption],
		[Category],
		[Keywords],
		[CreatedByUser],
		[CreatedDate],
		[UpdatedByUser],
		[UpdateDate],
		[ShortDescription],
		[MetadataXml]
	)
	SELECT 		ib.EntityName, 
		ib.CRMId,
		ib.[ImageId],
		ib.[DisplayOrder],
		im.[CrmImageId],
		im.[Caption],
		im.[Category],
		im.[Keywords],
		im.[CreatedByUser],
		im.[CreatedDate],
		im.[UpdatedByUser],
		im.[UpdateDate],
		ib.[ShortDescription],
		ib.[MetadataXml]

	FROM adn_CRMImageBook AS ib	LEFT OUTER JOIN adn_CRMImage2 AS im ON ib.ImageId = im.imageId 
		WHERE ib.EntityName = @EntityName AND ib.CRMId = @CRMId ORDER BY DisplayOrder

	SELECT @FirstRec = (@Page - 1) * @RecordsPerPage
	SELECT @LastRec = (@Page * @RecordsPerPage + 1)
	
	-- Now, RETURN the SET of paged records, plus, an indiciation of we
	-- have more records or not!
	SELECT *, (SELECT COUNT(*) FROM #TempItems) RecordCount
	FROM #TempItems
	WHERE ID > @FirstRec AND ID < @LastRec	ORDER BY ID
	
	-- Turn NOCOUNT back OFF
	SET NOCOUNT OFF
END
GO

CREATE PROCEDURE [dbo].[adn_GetSingleCRMImageBook]
(
	@EntityName 	nchar (50) ,
	@CRMId 		[uniqueidentifier],
	@ImageId 		[int ] 
	)
AS
	SELECT 
		ib.CRMId,
		ib.DisplayOrder,
		ib.ImageId,
		im.[CrmImageId],
		im.[Caption],
		im.[Category],
		im.[Keywords],
		im.[CreatedByUser],
		im.[CreatedDate],
		im.[UpdatedByUser],
		im.[UpdateDate],
		ib.[ShortDescription],
		ib.[MetadataXml]
	FROM adn_CRMImageBook AS ib	LEFT OUTER JOIN adn_CRMImage2 AS im ON ib.ImageId = im.imageId 
		WHERE ib.ImageId = @ImageId and ib.EntityName = @EntityName and ib.CRMId = @CRMId 
GO

-- if any exists get the number of images associated with this entity
CREATE PROCEDURE [dbo].[adn_GetCRMImageBookNb]
(
	@EntityName 	nchar (50) ,
	@CRMId 		[uniqueidentifier]
)
AS
BEGIN
	SELECT 
		EntityName,
		CRMID,
		COUNT (imageId) AS nb
		FROM adn_CRMImageBook 	
		WHERE 	EntityName = @EntityName AND CRMId = @CRMId 
		GROUP BY EntityName,CRMID
END
GO

-- All distinct entityname,CRMId's with number of images, paged according sent parameters
CREATE PROCEDURE [dbo].[adn_GetCRMAllImageBookPaged]
(
	@imageLibId int = 0,
	@Page int = 1,
	@RecordsPerPage int = 10
)
AS
BEGIN
-- Find out the first AND last record we want
DECLARE @FirstRec int, @LastRec int

--Create a temporary table
CREATE TABLE #TempItems
(
	ID		int IDENTITY,
	EntityName 	nchar (50) ,
	CRMId 		[uniqueidentifier],
	Cnt int
)

	-- We don't want to RETURN the # of rows inserted
	-- into our temporary table, so turn NOCOUNT ON
	SET NOCOUNT ON

	-- INSERT the rows FROM tblItems into the temp. table
	INSERT INTO	#TempItems
	(
		EntityName, CRMId, cnt
	)
	SELECT b.EntityName, b.CRMID, COUNT (b.imageId) AS nb
		FROM adn_CRMImageBook b INNER JOIN adn_CrmImage2 i ON b.imageId = i.imageId 
		WHERE i.imageLibId = @imageLibId
		GROUP BY EntityName,CRMID
		ORDER BY EntityName,CRMId

	SELECT @FirstRec = (@Page - 1) * @RecordsPerPage
	SELECT @LastRec = (@Page * @RecordsPerPage + 1)
	
	-- Now, RETURN the SET of paged records, plus, an indiciation of we
	-- have more records or not!
	SELECT *, (SELECT COUNT(*) FROM #TempItems) RecordCount
	FROM #TempItems
	WHERE ID > @FirstRec AND ID < @LastRec	ORDER BY ID
	
	-- Turn NOCOUNT back OFF
	SET NOCOUNT OFF
END
GO

CREATE PROCEDURE [dbo].[adn_GetAllCRMImagesEx]
(	@CRMId uniqueidentifier,
	@ImageLibId int = 0
)
AS
	SELECT 
		im.[ImageId], 
		im.[ImageLibId],
		im.[CrmImageId],
		im.[Caption],
		im.[Category],
		im.[Keywords],
		im.[CreatedByUser],
		im.[CreatedDate],
		im.[UpdatedByUser],
		im.[UpdateDate],
		im.[ShortDescription],
		im.[MetadataXml]
	FROM adn_CRMImage2 im WHERE 	(ImageLibId = @ImageLibId) AND (NOT ImageId IN (SELECT ImageId FROM adn_CRMImageBook WHERE CRMId = @CrmId ))
GO